package misc

import (
	"fmt"
	"reflect"
	"strconv"
	"strings"
	"time"

	"hub.hddznet.com/uniplatform/framework/logging"

	"github.com/chinahdkj/xorm"
	"github.com/tealeg/xlsx"
)

func XlsxImport(path string, importSheet string, importModel string, db *xorm.Engine, filedRow int) {

	xlFile, err := xlsx.OpenFile(path)

	if err != nil {
		panic("文件解析失败！")
	}

	var sheetName string

	if importSheet != "" {
		importSheet = strings.ToLower(importSheet)
	}

	for _, sheet := range xlFile.Sheets {

		sn := strings.ToLower(strings.TrimSpace(sheet.Name))

		//如果sheet不为空，则匹配需要插入的sheet
		if importSheet != "" {
			if sn != importSheet {
				continue
			}
		}

		sheetName = db.TableMapper.Obj2Table(sn)

		_, err := db.Table(sheetName).Count()

		if err != nil {
			logging.Error("表名不存在或执行失败：%s, %s", sheetName, err.Error())
			continue
		}

		if err == nil {

			session := db.NewSession()
			defer session.Close()

			err := session.Begin()

			if err != nil {
				logging.Error("")
			}

			//如果importModel等于truncate则清空表，重新插入
			if importModel == "truncate" {
				session.Sql(fmt.Sprintf("truncate table %s", sheetName)).Execute()
			}

			//开始解析每一行

			for idx, row := range sheet.Rows {

				if idx <= filedRow { //去除第一行
					continue
				}

				tvals := []interface{}{}

				//至少有一行，且至少第一列不为空
				if len(row.Cells) >= 1 {

					var _id string
					cells := []string{}
					values := []string{}

					cols := []string{}

					for i := 0; i < len(sheet.Rows[filedRow].Cells); i++ {
						cols = append(cols, strings.TrimSpace(sheet.Rows[filedRow].Cells[i].Value))
					}

					for i := 0; i < len(cols); i++ {

						col := cols[i]

						if col == "" {
							continue
						}

						col = strings.ToLower(strings.TrimSpace(col))

						v := ""

						if i < len(row.Cells) {
							cell := row.Cells[i]
							v = strings.TrimSpace(cell.Value)
						}

						// fmt.Println(i, col, len(sheet.Rows[filedRow].Cells))

						cells = append(cells, db.Quote(col))
						values = append(values, "?")

						if col == "_id" && len(v) > 0 {
							_id = v
						}

						if col == "created" || col == "changed" {
							t := strconv.FormatInt(time.Now().Unix(), 10)
							tvals = append(tvals, t)
							continue
						}

						if len(v) > 0 {
							tvals = append(tvals, v)
						} else {
							tvals = append(tvals, nil)
						}
					}

					if _id == "" {
						continue //空行跳过
					}

					count, err := session.Table(sheetName).Where("_id=?", _id).Count()

					if err != nil {
						logging.Error("ERROR: %s =>  COUNT %s \n \t\t%s", sheetName, _id, err.Error())
						session.Rollback()
						break
					}

					var sql string
					var args []interface{}

					if count > 0 {
						//Update existing row
						sql = fmt.Sprintf("update %s set "+strings.Join(cells, "=?, ")+"=? where _id=?", sheetName)
						args = append(tvals, _id)
					} else {
						sql = fmt.Sprintf("insert into %s (%s) values (%s) ",
							sheetName, strings.Join(cells, ","), strings.Join(values, ","))
						args = tvals
					}

					_, err = session.Sql(sql, args...).Execute()

					if err != nil {
						logging.Error("ERROR: %s =>  %s\n\t\t%s, %v", sheetName, err.Error(), sql, tvals)
						session.Rollback()
						break
					}
				}
			}

			if importModel == "check" {
				session.Rollback()
			} else {
				session.Commit()
			}
		}
	}
}

func XlsxExport(path string, tableName string, db *xorm.Engine, is []interface{}) {
	//设置需要导出的表名
	// tbs := []string{"gateway", "dict", "dict_item", "pipe", "analytics_index", "custom_service_record"}
	tbs := []string{"district", "zone", "group", "user", "factory_template",
		"role", "role_user", "factory", "fprocess", "fposition", "fmonitor", "fsensor",
		"factory_info", "dma_station", "station", "dma", "ssensor", "rtu", "stype",
		"gateway", "dict", "dict_item", "pipe", "analytics_index", "custom_service_record", "object", "domain", "domability", "menu"}

	tbFields, err := getTBFields(db, tbs)
	if err != nil {
		logging.Error(err.Error())
	}

	// tbFields := map[string][]string{
	// 	"district":              []string{"_id", "code", "type", "name", "sname", "status", "remark", "order", "created", "changed"},
	// 	"zone":                  []string{"_id", "code", "parent", "name", "sname", "status", "remark", "order", "created", "changed"},
	// 	"group":                 []string{"_id", "code", "zone", "type", "name", "sname", "parent", "root", "address", "position", "province", "city", "district", "zipcode", "linkman", "phone", "status", "remark", "order", "created", "changed"},
	// 	"user":                  []string{"_id", "sn", "name", "account", "password", "salt", "sex", "birth", "idcard", "status", "job", "email", "mobile", "smobile", "telephone", "group", "role", "direct", "duty", "post", "admin", "order", "created", "changed"},
	// 	"factory_template":      []string{"_id", "code", "type", "subtype", "process", "process_code", "position", "position_code", "monitor", "monitor_code", "monitor_type", "sensor", "sensor_code", "stype", "source", "required", "status", "order", "created", "changed"},
	// 	"role":                  []string{"_id", "name", "display", "group", "status", "order", "created", "changed"},
	// 	"role_user":             []string{"_id", "role", "user", "created", "changed"},
	// 	"factory":               []string{"_id", "code", "group", "zipcode", "name", "type", "subtype", "sname", "capacity", "address", "position", "status", "order", "created", "changed"},
	// 	"fprocess":              []string{"_id", "model", "code", "factory", "name", "sname", "status", "remark", "order", "created", "changed"},
	// 	"fposition":             []string{"_id", "model", "code", "factory", "process", "name", "sname", "status", "remark", "order", "created", "changed"},
	// 	"fmonitor":              []string{"_id", "model", "code", "factory", "process", "position", "type", "name", "sname", "sn", "status", "remark", "order", "created", "changed"},
	// 	"fsensor":               []string{"_id", "model", "code", "factory", "process", "position", "monitor", "stype", "name", "sname", "unit", "source", "confirm", "method", "virtual", "transform", "expression", "local_tag", "tag", "io", "signal", "cyclic", "upload", "precision", "status", "remark", "order", "created", "changed"},
	// 	"factory_info":          []string{"_id", "factory", "photos", "desc", "ptime", "area", "service_pop", "water_supply", "water_process", "pumps", "service_area"},
	// 	"dma_station":           []string{"_id", "dma", "source", "station", "sensor", "type", "status", "order", "created", "changed"},
	// 	"station":               []string{"_id", "order", "name", "sname", "code", "rtu", "msn", "sim", "district", "group", "dma", "type", "source", "caliber", "category", "function", "virtual", "address", "position", "cyclic", "upload", "status", "created", "changed"},
	// 	"dma":                   []string{"_id", "name", "code", "level", "parent", "group", "position", "area", "low", "flux", "accumulate", "status", "order", "created", "changed"},
	// 	"ssensor":               []string{"_id", "order", "name", "sname", "code", "stype", "station", "virtual", "transform", "expression", "tag", "source", "channel", "signal", "unit", "status", "precision", "remark", "created", "changed"},
	// 	"rtu":                   []string{"_id", "code", "type", "sn", "district", "group", "manual", "name", "station", "sim", "frequence", "cyclic", "status", "model", "power", "setup", "staff", "address", "position", "order", "created", "changed"},
	// 	"stype":                 []string{"_id", "code", "type", "name", "sname", "precision", "unit", "max", "min", "interval", "order", "created", "changed"},
	// 	"gateway":               []string{"_id", "name", "code", "district", "group", "factory", "type", "manual", "sn", "status", "mode", "ip", "ip", "port", "setup", "staff", "address", "position", "order", "created", "changed"},
	// 	"dict":                  []string{"_id", "code", "name", "remark", "order", "created", "changed"},
	// 	"dict_item":             []string{"_id", "code", "name", "value", "order", "created", "changed"},
	// 	"pipe":                  []string{"_id", "code", "position", "area", "lowest", "mnl_stime", "mnl_etime", "mdp", "flux", "accumulate", "status", "order", "created", "changed"},
	// 	"analytics_index":       []string{"_id", "subject", "scode", "type", "tcode", "code", "name", "sname", "remark", "level", "precision", "unit", "source", "confirm", "cyclic", "status", "created", "changed", "compute", "order"},
	// 	"custom_service_record": []string{"_id", "docno", "time", "category", "majority", "minor", "status", "address", "coord", "desc", "contact", "cellphone", "utype", "userial", "rtime", "ruser", "ptime", "puser", "ctime", "reason", "result", "auser", "atime", "created", "changed"},
	// }

	sheetSQL := map[string]string{}
	ketSheet := map[string]string{}
	tbField := map[string][]string{}
	for _, tb := range tbs {
		if tableName != "" {
			tableName = strings.ToLower(tableName)
			if tableName != tb {
				continue
			}
		}
		sheetSQL[tb] = fmt.Sprintf(`select * from %s`, db.TableMapper.Obj2Table(tb))
		ketSheet[tb] = tb
		tbField[tb] = tbFields[tb]
	}
	results := make(map[string][]map[string]interface{})
	for k, v := range sheetSQL {
		r, err := db.Sql(v).Query().List()
		if err != nil {
			logging.Error(err.Error())
		}
		results[k] = r
	}

	// _, results, err := db.Sqls(sheetSQL).Execute()

	// if err != nil {
	// 	logging.Error(err.Error())
	// }

	packge(tbs, results, tbFields) //补充字段，主要是那些为NULL的字段进行补充

	databook, err := xorm.NewDatabookWithData(
		ketSheet,
		results,
		true,
		tbField)

	if err != nil {
		logging.Error(err.Error())
	}

	err = databook.SaveAsXLSX(path, 0777)
	if err != nil {
		logging.Error(err.Error())
	}

}

func getTBFields(engine *xorm.Engine, tbs []string) (rst map[string][]string, err error) {
	exportTbNames := []string{}
	TBMap := map[string]string{}
	for _, tb := range tbs {
		sheetName := engine.TableMapper.Obj2Table(tb)
		TBMap[sheetName] = tb
		exportTbNames = append(exportTbNames, sheetName)
	}

	tables, err := engine.DBMetas()
	if err != nil {
		return nil, err
	}

	rst = make(map[string][]string)
	for _, table := range tables {
		if Include(exportTbNames, table.Name) {
			rst[TBMap[table.Name]] = []string{}
			for _, col := range table.Columns() {
				rst[TBMap[table.Name]] = append(rst[TBMap[table.Name]], col.Name)
			}
		}
	}

	return rst, nil

}

func packge(tbs []string, results map[string][]map[string]interface{}, tbFields map[string][]string) {
	for _, tb := range tbs {
		for _, r := range results[tb] {
			if len(r) < len(tbFields[tb]) {
				for _, k := range tbFields[tb] {
					if _, has := r[k]; !has {
						r[k] = ""
					}
				}
			}
		}
	}
}

//设置需要导出的表名

//配置文件导出
func XlsxExport2(path string, tableName string, db *xorm.Engine, is []interface{}) {
	//设置需要导出的表名
	tbs := []string{"district", "zone", "group", "user", "factory_template",
		"role", "role_user", "factory", "fprocess", "fposition", "fmonitor", "fsensor",
		"factory_info", "dma_station", "station", "dma", "ssensor", "rtu", "stype",
		"gateway", "dict", "dict_item", "pipe", "analytics_index", "custom_service_record"}

	// xlsx := excelize.NewFile()
	for _, tb := range tbs {
		if tableName != "" {
			tableName = strings.ToLower(tableName)
			if tableName != tb {
				continue
			}
		}

		tbName := db.TableMapper.Obj2Table(tb)
		sess := db.Table(tbName)

		_, err := sess.Count()

		if err != nil {
			logging.Error("表名不存在或执行失败：%s, %s", tbName, err.Error())
			continue
		}

		tp := getType(tb, is)

		// bean := reflect.New(tp).Interface()
		beans := reflect.New(reflect.SliceOf(tp))
		beans.Elem().Set(reflect.Indirect(reflect.MakeSlice(reflect.SliceOf(tp), 0, 0)))
		sess.Find(beans.Interface())
		fmt.Printf("%+v\n", beans)

		// Create a new sheet.
		// index := xlsx.NewSheet(tb)
		// for _, r := range rows {
		// 	// value := reflect.ValueOf(r)
		// 	// for i := 0; i < value.NumField(); i++ {
		// 	// 	fmt.Printf("Field %d: %v\n", i, value.Field(i))
		// 	// }
		// 	// // Set value of a cell.
		// 	// xlsx.SetCellValue(tb, "A2", "Hello world.")
		// 	// xlsx.SetCellValue("Sheet1", "B2", 100)
		// 	// // Set active sheet of the workbook.
		// 	// xlsx.SetActiveSheet(index)
		// 	// // Save xlsx file by the given path.
		// }

	}
}

func getType(tbName string, is []interface{}) reflect.Type {
	for _, str := range is {
		testType := reflect.Indirect(reflect.ValueOf(str)).Type()
		testTypeName := testType.Name()
		if strings.ToLower(testTypeName) == tbName {
			return testType
		}
	}

	return nil
}
